package win.devhu.onestation.utils;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Sqlite 数据库
 */
public class SqliteDBUtil {

	public static String DRIVERURL; // sqlite 驱动
	public static String INITSQL; // 建库初始化脚本
	public static String DBNAME; // sqlite 数据库名

	private static ResourceBundle rb = ResourceBundle.getBundle("db-config"); // 加载配置文件

	private static Logger logger = LoggerFactory.getLogger(SqliteDBUtil.class);

	// 使用静态块加载配置
	static {
		DBNAME = rb.getString("sqlite.db.name");
		INITSQL = rb.getString("sqlite.db.initsql");
		DRIVERURL = "jdbc:sqlite:";
		createDatabase(DBNAME); // 数据库不存在则创建
		initDBTables(INITSQL);
	}

	/**
	 * 建立数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		String connUrl = DRIVERURL + DBNAME;

		try {
			conn = DriverManager.getConnection(connUrl);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;

	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}

	/**
	 * 数据库不存在，则创建
	 */
	public static void createDatabase(String dbName) {

		File file = new File(dbName);

		if (file.exists()) {
			logger.info("This database name already exists");
		} else {
			String connUrl = DRIVERURL + dbName;
			try (Connection conn = DriverManager.getConnection(connUrl)) {
				if (conn != null) {
					DatabaseMetaData meta = conn.getMetaData();
					logger.info("The driver name is " + meta.getDriverName());
					logger.info("A new database has been created.");
				}

			} catch (SQLException e) {
				logger.error(e.getMessage());
			}
		}

	}

	/**
	 * 根据 init.sql 初始化数据库
	 */
	public static void initDBTables(String sqlFile) {
			
			try {
				String sql = new String(Files.readAllBytes(Paths.get(sqlFile)));
				
				logger.info(sql);
				
				String connUrl = DRIVERURL + DBNAME;

				try (Connection conn = DriverManager.getConnection(connUrl); Statement stmt = conn.createStatement()) {
					stmt.execute(sql);		//初始化数据库
				} catch (SQLException e) {
					logger.error(e.getMessage());
				}
			} catch (IOException e) {
				logger.error(e.getMessage());
				logger.info("This file is not exists");
			}
		
		} 
	}
